/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.1.3                     */
/* Target DBMS:           MS SQL Server 2005                              */
/* Project file:          MVC4Shop.dez                                    */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2012-09-20 17:30                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "Category"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Category] (
    [category_id] INTEGER IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(40),
    [image] VARCHAR(100),
    CONSTRAINT [PK_Category] PRIMARY KEY ([category_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Product"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Product] (
    [product_id] INTEGER IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(40),
    [image] VARCHAR(100),
    [price] DOUBLE PRECISION,
    [details] TEXT,
    [hit] INTEGER,
    [category_id] INTEGER,
    [createdate] DATETIME,
    CONSTRAINT [PK_Product] PRIMARY KEY ([product_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Color"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Color] (
    [color_id] INTEGER IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(40),
    [image] VARCHAR(100),
    CONSTRAINT [PK_Color] PRIMARY KEY ([color_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Size"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Size] (
    [size_id] INTEGER IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(40),
    CONSTRAINT [PK_Size] PRIMARY KEY ([size_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "ProductDetails"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE [ProductDetails] (
    [id] INTEGER IDENTITY(0,1) NOT NULL,
    [product_id] INTEGER,
    [size_id] INTEGER,
    [color_id] INTEGER,
    CONSTRAINT [PK_ProductDetails] PRIMARY KEY ([id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Role"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Role] (
    [role_id] INTEGER IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(40),
    CONSTRAINT [PK_Role] PRIMARY KEY ([role_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Member"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Member] (
    [member_id] INTEGER IDENTITY(0,1) NOT NULL,
    [username] VARCHAR(40),
    [password] VARCHAR(40),
    [email] VARCHAR(80),
    [createdate] DATETIME,
    [role_id] INTEGER,
    CONSTRAINT [PK_Member] PRIMARY KEY ([member_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "MemberDetails"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE [MemberDetails] (
    [id] INTEGER IDENTITY(0,1) NOT NULL,
    [member_id] INTEGER,
    [fullname] NVARCHAR(40),
    [address] NVARCHAR(40),
    [phone] VARCHAR(40),
    [birthday] DATETIME,
    CONSTRAINT [PK_MemberDetails] PRIMARY KEY ([id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Orders"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Orders] (
    [order_id] INTEGER IDENTITY(0,1) NOT NULL,
    [member_id] INTEGER,
    [quantity] INTEGER,
    [total] DOUBLE PRECISION,
    [createdate] DATETIME,
    CONSTRAINT [PK_Orders] PRIMARY KEY ([order_id])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "OrdersDetails"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE [OrdersDetails] (
    [id] INTEGER IDENTITY(0,1) NOT NULL,
    [order_id] INTEGER,
    [product_id] INTEGER,
    [price] DOUBLE PRECISION,
    [quantity] INTEGER,
    [size] NVARCHAR(40),
    [color] NVARCHAR(40),
    CONSTRAINT [PK_OrdersDetails] PRIMARY KEY ([id])
)
GO

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [Product] ADD CONSTRAINT [Category_Product] 
    FOREIGN KEY ([category_id]) REFERENCES [Category] ([category_id])
GO

ALTER TABLE [ProductDetails] ADD CONSTRAINT [Product_ProductDetails] 
    FOREIGN KEY ([product_id]) REFERENCES [Product] ([product_id])
GO

ALTER TABLE [ProductDetails] ADD CONSTRAINT [Color_ProductDetails] 
    FOREIGN KEY ([color_id]) REFERENCES [Color] ([color_id])
GO

ALTER TABLE [ProductDetails] ADD CONSTRAINT [Size_ProductDetails] 
    FOREIGN KEY ([size_id]) REFERENCES [Size] ([size_id])
GO

ALTER TABLE [Member] ADD CONSTRAINT [Role_Member] 
    FOREIGN KEY ([role_id]) REFERENCES [Role] ([role_id])
GO

ALTER TABLE [MemberDetails] ADD CONSTRAINT [Member_MemberDetails] 
    FOREIGN KEY ([member_id]) REFERENCES [Member] ([member_id])
GO

ALTER TABLE [Orders] ADD CONSTRAINT [Member_Orders] 
    FOREIGN KEY ([member_id]) REFERENCES [Member] ([member_id])
GO

ALTER TABLE [OrdersDetails] ADD CONSTRAINT [Orders_OrdersDetails] 
    FOREIGN KEY ([order_id]) REFERENCES [Orders] ([order_id])
GO

ALTER TABLE [OrdersDetails] ADD CONSTRAINT [Product_OrdersDetails] 
    FOREIGN KEY ([product_id]) REFERENCES [Product] ([product_id])
GO
